package databaseHelper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import datatypes.WorkedExam;

/**
 * 
 * Access to database for workedExam
 * 
 * @author Rupert
 *
 */
public class WorkedExamDAO {
	
	DataSource ds = DataSource.INSTANCE;

	/**
	 * 
	 * get List of worked exams to an exam
	 * 
	 * @param examId
	 * @return
	 */
	public List<WorkedExam> findAllofExam(int examId) {
		List<WorkedExam> allExams = new ArrayList<WorkedExam>();
		

		String query = "SELECT * FROM workedExam WHERE examId = "+examId;
		
		try{
			Statement st = ds.getStatement();
			ResultSet rs = st.executeQuery(query);
			
			WorkedExam curExam;
			
			while( rs.next()){
				curExam = new WorkedExam();
				curExam.setId(rs.getInt(1));
				curExam.setIdOfExam(rs.getInt(2));
				curExam.setStudentsName(rs.getString(3));
				curExam.setStudentsMatrNr(rs.getString(4));
				curExam.setGrade(rs.getDouble(5));
				curExam.setSecondGrade(rs.getDouble(6));
				

				query = "SELECT * FROM workedExam_archievedPoints WHERE workedExamId = "+curExam.getId()+" ORDER BY exerciseIndex ASC";
				String secondPointsQuery = "SELECT * FROM workedExam_archievedPoints_second WHERE workedExamId = "+curExam.getId()+" ORDER BY exerciseIndex ASC";
				
				Statement newSt = ds.getStatement();
				ResultSet newRS = newSt.executeQuery(query);
				
				Statement secondNewSt = ds.getStatement();
				ResultSet secondNewRS = secondNewSt.executeQuery(secondPointsQuery);
				
				int nbrOfExercises =0;
				while(newRS.next()){
					nbrOfExercises++;
				}
				
				double[] archievedPointsPerExercise = new double[nbrOfExercises];

				nbrOfExercises =0;
				while(secondNewRS.next()){
					nbrOfExercises++;
				}
				
				double[] archievedSecondPointsPerExercise = new double[nbrOfExercises];
				
				newRS.beforeFirst();
				secondNewRS.beforeFirst();
				
				int exerciseIndex =0;
				while( newRS.next()){
					archievedPointsPerExercise[exerciseIndex] = newRS.getDouble(3);
					exerciseIndex++;
				}
				
				exerciseIndex =0;
				while( secondNewRS.next()){
					archievedSecondPointsPerExercise[exerciseIndex] = secondNewRS.getDouble(3);
					exerciseIndex++;
				}
				
				curExam.setPointsPerExercise(archievedPointsPerExercise);
				curExam.setSecondPointsPerExercise(archievedSecondPointsPerExercise);
				allExams.add(curExam);
			}
			
				
		}catch(SQLException e){
			e.printStackTrace();
		}finally {
			ds.close();
		}
		return allExams;
    }
	
	
	/**
	 * grading key changes are calculated for the corresponding exams
	 * @param examId
	 */
	public void reloadGradesForWorkedExamOfExamId(int examId){
		List<WorkedExam> workedExams = findAllofExam(examId);
		
		for(WorkedExam curWorkedExam : workedExams){
			double achievedPoints = getPointsOfWorkedExam(curWorkedExam.getId());
			double achievedPointsSecond = getPointsSecondOfWorkedExam(curWorkedExam.getId());
			
			double grade = getGradeToPoints(examId, achievedPoints);
			double secondGrade = getGradeToPoints(examId, achievedPointsSecond);
			
			String updateWorkedExam = 
					"UPDATE workedExam " +
					"SET gradeDouble="+grade+", gradeSecondDouble="+secondGrade+" " +
					"WHERE id="+curWorkedExam.getId();
			
			execute(updateWorkedExam);
			
			
		}
		
		
	}
	
	/**
	 * getting achieved points of a worked exam
	 * @param workedExamId
	 * @return
	 */
	public double getPointsOfWorkedExam(int workedExamId){

		String query = "SELECT SUM(archievedPoints) FROM workedExam_archievedPoints WHERE workedExamId="+workedExamId;

		double achievedPoints = 0;
		
		Statement st;
		try {
			st = ds.getStatement();
			ResultSet rs = st.executeQuery(query);
			rs.next();
			achievedPoints = rs.getDouble(1);
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		
		return achievedPoints;
	}
	
	/**
	 * getting achieved points (second correction) of a worked exam
	 * @param workedExamId
	 * @return
	 */
	public double getPointsSecondOfWorkedExam(int workedExamId){

		String query = "SELECT SUM(achievedPoints) FROM workedExam_archievedPoints_second WHERE workedExamId="+workedExamId;

		double achievedPoints = 0;
		
		Statement st;
		try {
			st = ds.getStatement();
			ResultSet rs = st.executeQuery(query);
			rs.next();
			achievedPoints = rs.getDouble(1);
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		
		return achievedPoints;
	}
	
	/**
	 * 
	 * gives the grade to an exam and number of points
	 * 
	 * @param examId
	 * @param achievedPoints
	 * @return
	 */
	public double getGradeToPoints(int examId, double achievedPoints){

		String query = "SELECT MIN(grade) FROM exam_grading WHERE examId="+examId+" AND pointsNeeded<="+achievedPoints;

		double grade = 5.0;
		
		Statement st;
		try {
			st = ds.getStatement();
			ResultSet rs = st.executeQuery(query);
			rs.next();
			grade = rs.getDouble(1);
			if(grade == 0){
				grade = 5;
			}
			
		} catch (SQLException e) {
			 grade = 5.0;
		}
		
		
		return grade;
	}
	
	
	/**
	 * 
	 * @param grade
	 * @return number of Exams with the given grade
	 */
	public int getNumberOfGrades(double grade, int examId){
		String query =	"SELECT count(*) " +
						"FROM workedExam " +
						"WHERE examId = "+examId+" "+
						"AND gradeDouble ="+grade;
		int count = 0;
		if(	grade == 1.0 || grade == 1.3 || grade == 1.7 || 
			grade == 2.0 || grade == 2.3 || grade == 2.7 || 
			grade == 3.0 || grade == 3.3 || grade == 3.7 || 
			grade == 4.0 || grade == 4.3 || grade == 4.7 || 
			grade == 5.0){
			try{
				
				Statement st = ds.getStatement();
				ResultSet rs = st.executeQuery(query);
				if(rs.next())
					count = rs.getInt(1);
				
			}catch(SQLException e){
				
			}finally {
				ds.close();
			}
			return count;
		}
		return count;
		
	}
	
	/**
	 * 
	 * @param grade
	 * @return number of Exams with the given grade
	 */
	public int getNumberOfGradesSecond(double grade, int examId){
		String query =	"SELECT count(*) " +
						"FROM workedExam " +
						"WHERE examId = "+examId+" "+
						"AND gradeSecondDouble ="+grade;
		int count = 0;
		if(	grade == 1.0 || grade == 1.3 || grade == 1.7 || 
			grade == 2.0 || grade == 2.3 || grade == 2.7 || 
			grade == 3.0 || grade == 3.3 || grade == 3.7 || 
			grade == 4.0 || grade == 4.3 || grade == 4.7 || 
			grade == 5.0){
			try{
				
				Statement st = ds.getStatement();
				ResultSet rs = st.executeQuery(query);
				if(rs.next())
					count = rs.getInt(1);
				
			}catch(SQLException e){
				
			}finally {
				ds.close();
			}
			return count;
		}
		return count;
		
	}
	
	/**
	 * 
	 * a list containing all achieved points
	 * 
	 * @param examId
	 * @return
	 */
	public List<Double> getAchievedPointsList(int examId){
		String query =	"SELECT id " +
				"FROM workedExam " +
				"WHERE examId = "+examId;
		
		List<Double> achievedPointsInList = new ArrayList<Double>();
		
		try {
			Statement st = ds.getStatement();
			ResultSet rs = st.executeQuery(query);
			
			while(rs.next()){
				query =	"SELECT SUM(archievedPoints) " +
						"FROM workedExam_archievedPoints " +
						"WHERE workedExamId = "+rs.getInt(1);

				Statement st_workedExam = ds.getStatement();
				ResultSet rs_workedExam = st_workedExam.executeQuery(query);
				
				if(rs_workedExam.next()){
					achievedPointsInList.add(rs_workedExam.getDouble(1));
				}
				
			}
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return achievedPointsInList;
	}
	
	/**
	 * 
	 * a list containing all achieved points
	 * 
	 * @param examId
	 * @return
	 */
	public List<Double> getAchievedPointsListSecond(int examId){
		String query =	"SELECT id " +
				"FROM workedExam " +
				"WHERE examId = "+examId;
		
		List<Double> achievedPointsInList = new ArrayList<Double>();
		
		try {
			Statement st = ds.getStatement();
			ResultSet rs = st.executeQuery(query);
			
			while(rs.next()){
				query =	"SELECT SUM(achievedPoints) " +
						"FROM workedExam_archievedPoints_second " +
						"WHERE workedExamId = "+rs.getInt(1);

				Statement st_workedExam = ds.getStatement();
				ResultSet rs_workedExam = st_workedExam.executeQuery(query);
				
				if(rs_workedExam.next()){
					achievedPointsInList.add(rs_workedExam.getDouble(1));
				}
				
			}
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return achievedPointsInList;
	}
	
	/**
	 * Calculates how many exams have been worked!
	 * 
	 * @param examId
	 * @return 
	 */
	public int getNumberOfWorkedExams(int examId){
		String query =	"SELECT count(*) " +
				"FROM workedExam " +
				"WHERE examId = "+examId;
		int count = 0;
		try{
			Statement st = ds.getStatement();
			ResultSet rs = st.executeQuery(query);
			if(rs.next())
				count = rs.getInt(1);
			
		}catch(SQLException e){
			
		}finally {
			ds.close();
		}
		return count;
	}
	
	/**
	 * 
	 * @param examId
	 * @return a double[] with the average points for each exercise of a Exam ( examId)
	 */
	public double[] getAveragePoints(int examId){
		System.out.println("getAveragePoints in DAO!");

		int numberOfWorkedExams = -1;
		String query =	"SELECT count(*) " +
				"FROM workedExam " +
				"WHERE examId = "+examId;
		
		try{
			Statement st = ds.getStatement();
			ResultSet rs = st.executeQuery(query);
			rs.next();
			numberOfWorkedExams = rs.getInt(1);
		
		}catch(SQLException e){
			e.printStackTrace();
		}finally {
			ds.close();
		}
		
		ExamDAO examDAO = new ExamDAO();
		int nbrOfExercises = examDAO.getNumberOfExercises(examId);
		
		double[] averageArray = new double[nbrOfExercises];
		
		if(numberOfWorkedExams<1){
			return averageArray;
		}

		
		

		String orClause = "";
		
		query =	"SELECT id " +
				"FROM workedExam " +
				"WHERE examId = "+examId;
		
		try{
			Statement st = ds.getStatement();
			ResultSet rs = st.executeQuery(query);	
			
			int i=0;
			while(rs.next()){
				int workedExamId = rs.getInt(1);
				
				if(i==0){
					orClause = "AND (workedExamId="+workedExamId;
				}else{
					orClause = orClause + " OR workedExamId ="+workedExamId;
				}
				i++;
			}
			
			orClause = orClause + ")";
			
		}catch(SQLException e){
			return null;
		}finally {
			ds.close();
		}
		
		
		for(int i=0; i<nbrOfExercises; i++){
			try{
				query = "SELECT AVG(archievedPoints) FROM workedExam_archievedPoints WHERE exerciseIndex="+(i+1)+" "+orClause;

				
				Statement st = ds.getStatement();
				ResultSet rs = st.executeQuery(query);
				
				rs.next();
				
				double average = rs.getDouble(1);
				
				averageArray[i] = average;
				
				System.out.println("Aufgabe "+i+": "+average);
				
			}catch(SQLException e){
				averageArray[i] = 0;
				e.printStackTrace();
			}finally {
				ds.close();
			}
		}
		
		return averageArray;
		
	}
	
	/**
	 * 
	 * @param examId
	 * @return a double[] with the average points for each exercise of a Exam ( examId)
	 */
	public double[] getAveragePointsSecond(int examId){
		System.out.println("getAveragePoints in DAO!");

		int numberOfWorkedExams = -1;
		String query =	"SELECT count(*) " +
				"FROM workedExam " +
				"WHERE examId = "+examId;
		
		try{
			Statement st = ds.getStatement();
			ResultSet rs = st.executeQuery(query);
			rs.next();
			numberOfWorkedExams = rs.getInt(1);
		
		}catch(SQLException e){
			e.printStackTrace();
		}finally {
			ds.close();
		}
		
		ExamDAO examDAO = new ExamDAO();
		int nbrOfExercises = examDAO.getNumberOfExercises(examId);
		
		double[] averageArray = new double[nbrOfExercises];
		
		if(numberOfWorkedExams<1){
			return averageArray;
		}

		
		

		String orClause = "";
		
		query =	"SELECT id " +
				"FROM workedExam " +
				"WHERE examId = "+examId;
		
		try{
			Statement st = ds.getStatement();
			ResultSet rs = st.executeQuery(query);	
			
			int i=0;
			while(rs.next()){
				int workedExamId = rs.getInt(1);
				
				if(i==0){
					orClause = "AND (workedExamId="+workedExamId;
				}else{
					orClause = orClause + " OR workedExamId ="+workedExamId;
				}
				i++;
			}
			
			orClause = orClause + ")";
			
		}catch(SQLException e){
			return null;
		}finally {
			ds.close();
		}
		
		
		for(int i=0; i<nbrOfExercises; i++){
			try{
				query = "SELECT AVG(achievedPoints) FROM workedExam_archievedPoints_second WHERE exerciseIndex="+(i+1)+" "+orClause;

				
				Statement st = ds.getStatement();
				ResultSet rs = st.executeQuery(query);
				
				rs.next();
				
				double average = rs.getDouble(1);
				
				averageArray[i] = average;
				
				System.out.println("Aufgabe "+i+": "+average);
				
			}catch(SQLException e){
				averageArray[i] = 0;
				e.printStackTrace();
			}finally {
				ds.close();
			}
		}
		
		return averageArray;
		
	}
	
	
	    
    /**
     * 
     * @param sql
     * @return
     */
    private boolean execute(String sql) {

    	try {
            Statement stmt = ds.getStatement();
            stmt.execute(sql);
            if (stmt != null) {
                stmt.close();
            }
            
            return true;
        } catch (SQLException e) {
        	e.printStackTrace();
            return false;
        } finally {
            ds.close();
        }
    }
}


